package org.lq.system.dao.impl;

import lombok.extern.log4j.Log4j;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.lq.system.dao.RoleInfoDao;
import org.lq.system.entity.RoleInfo;
import org.lq.util.CastUtil;
import org.lq.util.JDBCUtil;

import java.sql.SQLException;
import java.util.List;

/**
 * @author 李冠良
 * @create 2020-10-13
 */
@Log4j
public class RoleInfoDaoImpl implements RoleInfoDao {
    /**
     * t添加
     *
     * @param roleInfo
     * @return
     */
    @Override
    public int save(RoleInfo roleInfo) {
        log.info("-->开始执行数据访问层, [RoleInfoDaoImpl]->save");
        int num = 0;
        QueryRunner queryRunner = new QueryRunner(JDBCUtil.getDataSource());
        try {
            num = queryRunner.update("INSERT INTO `ems`.`role_info`(`role_name`, `role_desc`, `role_state`) VALUES (?, ?, ?)",
                    roleInfo.getRoleName(), roleInfo.getRoleDesc(), roleInfo.getRoleState());
        } catch (SQLException e) {
            log.error("添加角色信息出现异常", e);
        }
        log.info("-->执行结果: " + num);
        log.info("-->数据访问层, [RoleInfoDaoImpl]->save,执行结束");
        return num;
    }

    /**
     * 修改
     *
     * @param roleInfo
     * @return
     */
    @Override
    public int update(RoleInfo roleInfo) {
        log.info("-->开始执行数据访问层, [RoleInfoDaoImpl]->update");
        int num = 0;
        QueryRunner queryRunner = new QueryRunner(JDBCUtil.getDataSource());
        try {
            num = queryRunner.update("UPDATE `ems`.`role_info` SET `role_name` = ?, `role_desc` = ?, `role_state` = ? WHERE `role_id` = ?",
                    roleInfo.getRoleName(), roleInfo.getRoleDesc(), roleInfo.getRoleState(), roleInfo.getRoleId());
        } catch (SQLException e) {
            log.error("修改角色信息出现异常", e);
        }
        log.info("-->执行结果: " + num);
        log.info("-->数据访问层, [RoleInfoDaoImpl]->update,执行结束");
        return num;
    }

    /**
     * 删除
     *
     * @param id
     * @return
     */
    @Override
    public int delete(int id) {
        log.info("-->开始执行数据访问层, [RoleInfoDaoImpl]->delete");
        int num = 0;
        QueryRunner queryRunner = new QueryRunner(JDBCUtil.getDataSource());
        try {
            num = queryRunner.update("delete from `ems`.`role_info` where `role_id` = ?", id);
        } catch (SQLException e) {
            log.error("删除角色信息出现异常", e);
        }
        log.info("-->执行结果: " + num);
        log.info("-->数据访问层, [RoleInfoDaoImpl]->delete,执行结束");
        return num;
    }

    /**
     * 通过ID查询
     *
     * @param id
     * @return
     */
    @Override
    public RoleInfo getById(int id) {
        log.info("-->开始执行数据访问层, [RoleInfoDaoImpl]->getById");
        RoleInfo roleInfo = null;
        QueryRunner queryRunner = new QueryRunner(JDBCUtil.getDataSource());
        try {
            roleInfo = queryRunner.query("select * from `ems`.`view_role_info` where id = ?", new BeanHandler<>(RoleInfo.class), id);
        } catch (SQLException e) {
            log.error("根据id查找角色出现异常",e);
        }
        log.info("执行结果: " + roleInfo);
        log.info("-->数据访问层, [RoleInfoDaoImpl]->getById,执行结束");
        return roleInfo;
    }

    /**
     * 总行数
     *
     * @return
     */
    @Override
    public int getCount() {
        log.info("-->开始执行数据访问层, [RoleInfoDaoImpl]->getCount()");
        int count = 0;
        QueryRunner queryRunner = new QueryRunner(JDBCUtil.getDataSource());

        try {
            count = queryRunner.query("select count(1) from `ems`.`view_role_info`", new ScalarHandler<Long>()).intValue();
        } catch (SQLException e) {
            log.error("获得总行数(无参数)出现异常",e);
        }
        log.info("执行结果: "+ count);
        log.info("-->数据访问层, [RoleInfoDaoImpl]->getCount(无参数),执行结束");
        return count;
    }

    /**
     * 分页查询
     *
     * @param startIndex
     * @param pageSize
     * @return
     */
    @Override
    public List<RoleInfo> pageList(int startIndex, int pageSize) {
        log.info("-->开始执行数据访问层, [RoleInfoDaoImpl]->pageList(int startIndex, int pageSize)");
        List<RoleInfo> list = null;
        QueryRunner queryRunner = new QueryRunner(JDBCUtil.getDataSource());
        try {
            list = queryRunner.query("select * from `ems`.`view_role_info` limit ?,?", new BeanListHandler<>(RoleInfo.class), startIndex, pageSize);
        } catch (SQLException e) {
            log.error("分页查询出现异常",e);
        }
        log.info("执行结果: " + list);
        log.info("-->数据访问层, [RoleInfoDaoImpl]->pageList(int startIndex, int pageSize),执行结束");
        return list;
    }

    /**
     * 根据条件查询总行数
     *
     * @param values
     * @return
     */
    @Override
    public int getCount(String... values) {
        log.info("-->开始执行数据访问层, [RoleInfoDaoImpl]->getCount(String roleName)");
        int count = 0;
        QueryRunner queryRunner = new QueryRunner(JDBCUtil.getDataSource());

        try {
            count = queryRunner.query("select count(1) from `ems`.`view_role_info` where roleState=? and `roleName` like ?",
                    new ScalarHandler<Long>(), CastUtil.castInt(values[1]),"%"+values[0]+"%").intValue();
        } catch (SQLException e) {
            log.error("获得总行数(String roleName)出现异常",e);
        }
        log.info("执行结果: "+ count);
        log.info("-->数据访问层, [RoleInfoDaoImpl]->getCount(String roleName),执行结束");
        return count;
    }

    /**
     * 根据条件分页查询
     *
     * @param startIndex
     * @param pageSize
     * @param value
     * @return
     */
    @Override
    public List<RoleInfo> pageByValues(int startIndex, int pageSize, String... value) {
        log.info("-->开始执行数据访问层, [RoleInfoDaoImpl]->pageList(int startIndex, int pageSize, String roleName)");
        List<RoleInfo> list = null;
        QueryRunner queryRunner = new QueryRunner(JDBCUtil.getDataSource());
        try {
            list = queryRunner.query("select * from `ems`.`view_role_info` where roleState =? and `roleName` like ? limit ?,?",
                    new BeanListHandler<>(RoleInfo.class), CastUtil.castInt(value[1]),"%"+value[0]+"%",startIndex, pageSize);
        } catch (SQLException e) {
            log.error("分页查询(int startIndex, int pageSize, String roleName)出现异常",e);
        }
        log.info("执行结果: " + list);
        log.info("-->数据访问层, [RoleInfoDaoImpl]->pageList(int startIndex, int pageSize, String roleName),执行结束");
        return list;
    }


    /**
     * 根据角色姓名模糊查询数据的总行数
     *
     * @param roleName
     * @return
     */
    @Override
    public int getCount(String roleName) {
        log.info("-->开始执行数据访问层, [RoleInfoDaoImpl]->getCount(String roleName)");
        int count = 0;
        QueryRunner queryRunner = new QueryRunner(JDBCUtil.getDataSource());

        try {
            count = queryRunner.query("select count(1) from `ems`.`view_role_info` where `roleName` like ?",
                    new ScalarHandler<Long>(),"%"+roleName+"%").intValue();
        } catch (SQLException e) {
            log.error("获得总行数(String roleName)出现异常",e);
        }
        log.info("执行结果: "+ count);
        log.info("-->数据访问层, [RoleInfoDaoImpl]->getCount(String roleName),执行结束");
        return count;
    }

    /**
     * 根据角色姓名模糊查询的数据
     *
     * @param startIndex
     * @param pageSize
     * @param roleName
     * @return
     */
    @Override
    public List<RoleInfo> pageByValues(int startIndex, int pageSize, String roleName) {
        log.info("-->开始执行数据访问层, [RoleInfoDaoImpl]->pageList(int startIndex, int pageSize, String roleName)");
        List<RoleInfo> list = null;
        QueryRunner queryRunner = new QueryRunner(JDBCUtil.getDataSource());
        try {
            list = queryRunner.query("select * from `ems`.`view_role_info` where `roleName` like ? limit ?,?",
                    new BeanListHandler<>(RoleInfo.class), "%"+roleName+"%",startIndex, pageSize);
        } catch (SQLException e) {
            log.error("分页查询(int startIndex, int pageSize, String roleName)出现异常",e);
        }
        log.info("执行结果: " + list);
        log.info("-->数据访问层, [RoleInfoDaoImpl]->pageList(int startIndex, int pageSize, String roleName),执行结束");
        return list;
    }

    /**
     * 根据角色变化,修改角色状态
     *
     * @param id
     * @param state
     * @return
     */
    @Override
    public int updateRoleById(int id, int state) {
        log.info("-->开始执行数据访问层, [RoleInfoDaoImpl]->update");
        int num = 0;
        QueryRunner queryRunner = new QueryRunner(JDBCUtil.getDataSource());
        try {
            num = queryRunner.update("UPDATE `ems`.`role_info` SET `role_state` = ? WHERE `role_id` = ?",
                    state,id);
        } catch (SQLException e) {
            log.error("修改角色信息出现异常", e);
        }
        log.info("-->执行结果: " + num);
        log.info("-->数据访问层, [RoleInfoDaoImpl]->update,执行结束");
        return num;
    }
}
